/**
 * 
 */
package sk.stuba.fiit.foo07.genex.common;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.derby.tools.ij;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import sk.stuba.fiit.foo07.genex.beans.Answer;
import sk.stuba.fiit.foo07.genex.beans.Question;
import sk.stuba.fiit.foo07.genex.dao.AnswerDao;
import sk.stuba.fiit.foo07.genex.dao.AnswerDaoDerby;
import sk.stuba.fiit.foo07.genex.dao.QuestionDao;
import sk.stuba.fiit.foo07.genex.dao.QuestionDaoDerby;

/**
 * @author Martin
 * 
 */
public class DerbyDBTools {

    public static int runScript(Connection con, String resourceFilePath)
            throws UnsupportedEncodingException, NullPointerException {

        InputStream in = new DerbyDBTools().getClass().getResourceAsStream(
                resourceFilePath);

        return ij.runScript(con, in, "UTF8", System.out, "UTF8");
    }

    public static boolean databaseInitialized(Connection con) {
        try {
            boolean ret = true;
            if (DerbyDBTools.runScript(con, "/ddl/dbCheck.sql") != 0)
                return false;

            // TODO check initial data

            return ret;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * Loads questions/answers from xml file into database
     * 
     * @param con -
     *                database connection
     * @param xmlPath -
     *                path to xml resource
     * @throws SQLException
     * @throws UnsupportedEncodingException
     */
    public static void loadData(Connection con, String xmlPath)
            throws SQLException, UnsupportedEncodingException {
        if (con == null)
            throw new NullPointerException(
                    "Database connection not initialized");

        if (DerbyDBTools.databaseInitialized(con) != true) {
            throw new SQLException(
                    "Database not initialized with default tables");
        }

        Question toImport = new Question();
        PreparedStatement pst = null;
        ResultSet rs = null;
        int importCategoryID = 0;
        try {
            pst = con
                    .prepareStatement("SELECT userID FROM TUSER WHERE login = ?");
            pst.setString(1, "importer");

            rs = pst.executeQuery();
            rs.next();
            toImport.setUserID(rs.getInt(1));
        } catch (SQLException e) {
            throw new SQLException("User for importing does not exist");
        }
        try {
            pst = con
                    .prepareStatement("SELECT questionTypeID FROM QuestionType WHERE name = ?");
            pst.setString(1, "Single choice");
            rs = pst.executeQuery();
            rs.next();
            toImport.setQuestionTypeID(rs.getInt(1));
        } catch (SQLException e) {
            throw new SQLException("QuestionType for importing does not exist");
        }
        try {
            pst = con
                    .prepareStatement("SELECT categoryID FROM Category WHERE name = ?");
            pst.setString(1, "Import");

            rs = pst.executeQuery();

            rs.next();
            importCategoryID = rs.getInt(1);
        } catch (SQLException e) {
            throw new SQLException("Category for importing does not exist");
        }
        try {
            DocumentBuilderFactory factory = DocumentBuilderFactory
                    .newInstance();
            DocumentBuilder parser = factory.newDocumentBuilder();
            Node doc = parser.parse(new DerbyDBTools().getClass()
                    .getResourceAsStream(xmlPath));
            followNode(con, doc, toImport, importCategoryID);

        } catch (SAXException e) {
            System.out.println(xmlPath + " is not well-formed.");
        } catch (IOException e) {
            System.out
                    .println("Due to an IOException, the parser could not check "
                            + xmlPath);
        } catch (FactoryConfigurationError e) {
            System.out.println("Could not locate a factory class");
        } catch (ParserConfigurationException e) {
            System.out.println("Could not locate a JAXP parser");
        }
    }

    private static void followNode(Connection con, Node node,
            Question toImport, int importCategoryID) {

        if (node.getNodeName().equals("question")) {
            NodeList children = node.getChildNodes();
            ArrayList<Answer> answers = new ArrayList<Answer>();
            for (int i = 0; i < children.getLength(); i++) {
                Node item = children.item(i);
                if (item.getNodeName().equals("text")) {
                    toImport.setText(item.getFirstChild().getNodeValue());
                }

                if (item.getNodeName().equals("answers")) {
                    Node ansNode = item.getFirstChild();

                    while (ansNode != null) {
                        Answer tmp = new Answer();
                        if (ansNode.getNodeName().equals("answer")) {
                            tmp.setText(ansNode.getFirstChild().getNodeValue());
                            NamedNodeMap nnm = ansNode.getAttributes();
                            tmp.setIsCorrect(Short.valueOf(
                                    nnm.getNamedItem("correct").getNodeValue())
                                    .equals(new Short((short) 1)));
                            answers.add(tmp);
                        }

                        ansNode = ansNode.getNextSibling();
                    }
                }
            }
            NamedNodeMap nnm = node.getAttributes();
            toImport.setDifficulty(Integer.valueOf(nnm.getNamedItem(
                    "difficulty").getNodeValue()));

            toImport.setCreated(new Timestamp(Calendar.getInstance()
                    .getTimeInMillis()));
            toImport.setLastUpdate(new Timestamp(Calendar.getInstance()
                    .getTimeInMillis()));

            QuestionDao questionDao = new QuestionDaoDerby(con);
            //TODO 
            //pridane odchytavanie vynimky (palo)
            try {
                questionDao.addQuestion(importCategoryID, toImport);

                AnswerDao ansDao = new AnswerDaoDerby(con);

                ansDao.addAnswersToQuestion(toImport.getQuestionID(), answers);
            } catch (Exception e) {
            }
        } else if (node.hasChildNodes()) {
            Node firstChild = node.getFirstChild();
            followNode(con, firstChild, toImport, importCategoryID);
        }

        Node nextNode = node.getNextSibling();
        if (nextNode != null)
            followNode(con, nextNode, toImport, importCategoryID);

    }

}
